# -*- coding: utf-8 -*-
__author__ = 'liubei'

import xlrd
import xlwt
from xlutils.copy import copy
import db_engine
import api_statistics
import aes
from datetime import datetime
import db_dashboard
import az_dateutil
import pytz
import time
import rest
import surveyInstanceDB
import topic
from openpyxl import load_workbook

msgSheetIndex = 5
detailedAdheranceDateIndex = 4
adheranceDateIndex = 3
userOperationReportIndex = 2

pageLength = 250

def open_excelByOpenXL(file='file.xlsx'):
    db_engine.connect("az", host=db_engine.host)
    wb = load_workbook(file)
    cpTable = wb._sheets[msgSheetIndex]
    for i in range(3, 66, 1):
        msgNo = int(cpTable.cell(row=i, column=1).value)
        print(msgNo)
        lMsgs = api_statistics.LongMessagePopularity.objects(msgNo=str(msgNo))
        userCount = 0
        if len(lMsgs) == 0:
            lMsg = api_statistics.LongMessagePopularity(totalCount=0)
        else:
            lMsg = lMsgs[0]
            userCount = len(lMsg.users)
        cpTable.cell(row=i, column=2, value=lMsg.totalCount)
        cpTable.cell(row=i, column=3, value=userCount)
        if userCount == 0:
            cpTable.cell(row=i, column=4, value=userCount)
        else:
            cpTable.cell(row=i, column=4, value="=C%s/B%s" % (i, i))

    for i in range(3, 11, 1):
        index = i + 8
        print 'index %s' % index
        lCats = api_statistics.LongMessageCatalogPopularity.objects(catalogNo=str(index))
        userCount = 0
        if len(lCats) == 0:
            lCat = api_statistics.LongMessageCatalogPopularity(totalCount=0)
        else:
            lCat = lCats[0]
            userCount = len(lCat.users)
        cpTable.cell(row=i, column=10, value=lCat.totalCount)
        cpTable.cell(row=i, column=11, value=userCount)
        if userCount == 0:
            cpTable.cell(row=i, column=12, value=0)
        else:
            cpTable.cell(row=i, column=12, value="=K%s/J%s" % (i, i))

    cpDetailedAdheranceDateTable = wb._sheets[detailedAdheranceDateIndex]
    cpAdheranceDateTable =  wb._sheets[adheranceDateIndex]
    cpUserOperationReportTable =  wb._sheets[userOperationReportIndex]
    users = db_engine.User.objects(userType__ne=2).order_by('createDate')
    print "===== total count : %s =====" % users.count()
    index = 4
    aesCipher = aes.AESCipher(aes.aes_key)
    healthDic = {}
    for i in range(0, users.count()):
        user = users[i]
        print user.patientCode
        if True:
            print 'index %s' % index
            # patientCode
            cpDetailedAdheranceDateTable.cell(row=2, column=index, value=user.patientCode)
            cpAdheranceDateTable.cell(row=2, column=index, value=user.patientCode)
            cpUserOperationReportTable.cell(row=2, column=index, value=user.patientCode)
            # sexy
            cpDetailedAdheranceDateTable.cell(row=3, column=index, value="Male" if aesCipher.decrypt(user.userGender) == "1" else "Female")
            cpAdheranceDateTable.cell(row=3, column=index, value="Male" if aesCipher.decrypt(user.userGender) == "1" else "Female")
            # year old
            birthday = aesCipher.decrypt(user.userBirthday)
            if birthday:
                print birthday
                try:
                    if "-" in birthday:
                        birthdayDate = datetime.strptime(birthday, '%Y-%m-%d')
                    elif "." in birthday:
                        birthdayDate = datetime.strptime(birthday, '%Y.%m.%d')
                    else:
                        birthdayDate = datetime.strptime(birthday, '%Y%m%d')
                    cpDetailedAdheranceDateTable.cell(row=4, column=index, value=calculate_age(birthdayDate))
                    cpAdheranceDateTable.cell(row=4, column=index, value=calculate_age(birthdayDate))
                    cpUserOperationReportTable.cell(row=4, column=index, value=calculate_age(birthdayDate))
                except Exception as err:
                    print str(err)

            # Adherance day since registration
            medicines = db_engine.Medicine.objects(userPhone=user.userPhone).order_by('-createDate')
            count = 0
            medicineList = []
            for medicine in medicines:
                if aesCipher.decrypt(medicine.done) == "1":
                    medicineList.append(az_dateutil.timestamp2datetime(medicine.createDate).strftime("%d %b %Y"))
                    count += 1
            cpDetailedAdheranceDateTable.cell(row=5, column=index, value=count)

            # number of days since registration
            createDate = az_dateutil.timestamp2datetime(user.createDate)
            diffCreateDate = diffDay(createDate)
            cpDetailedAdheranceDateTable.cell(row=6, column=index, value=diffCreateDate)
            cpUserOperationReportTable.cell(row=18, column=index, value=diffCreateDate)
            cpUserOperationReportTable.cell(row=10, column=index, value=int(diffCreateDate/7))
            cpDetailedAdheranceDateTable.cell(row=7, column=index, value="=%s/%s" % (excel_style(5, index), excel_style(6, index)))
            for i in range(0, len(medicineList), 1):
                cpDetailedAdheranceDateTable.cell(row=8 + i, column=index, value=medicineList[i])

            # Medicine Adherance(Adherance day in last 30 days)
            now = int(time.time())
            tz = pytz.timezone(pytz.country_timezones('cn')[0])
            thisDate = datetime.fromtimestamp(now, tz)
            now = int(az_dateutil.date2timestamp(
                thisDate.replace(thisDate.year, thisDate.month, thisDate.day, 0, 0, 0))) + 16 * 60 * 60

            medicines30 = db_dashboard.queryMedicinelistBetweenDays(now, now - 30 * 24 * 60 * 60, user.userPhone)
            count = 0
            medicineList = []
            for medicine in medicines30:
                if aesCipher.decrypt(medicine.done) == "1":
                    medicineList.append(az_dateutil.timestamp2datetime(medicine.createDate).strftime("%d %b %Y"))
                    count += 1
            cpAdheranceDateTable.cell(row=5, column=index, value=count)
            cpUserOperationReportTable.cell(row=23, column=index, value=count)
            for i in range(0, len(medicineList), 1):
                cpAdheranceDateTable.cell(row=6 + i, column=index, value=medicineList[i])

            # 是否设置了看护者
            caregivers = db_engine.Caregiver.objects(caredUserPhone=user.userPhone)
            cpUserOperationReportTable.cell(row=3, column=index, value="Y" if len(caregivers) > 0 else "N")
            cpUserOperationReportTable.cell(row=11, column=index, value="Y" if user.isSmoke == 1 else "N")
            cpUserOperationReportTable.cell(row=12, column=index, value="Y" if user.hasHighBp == 1 else "N")
            # 血压值(最近一次输入的高血压值)
            bps = db_dashboard.Bp.objects(userPhone=user.userPhone)
            if len(bps) == 0:
                cpUserOperationReportTable.cell(row=24, column=index, value=0)
                cpUserOperationReportTable.cell(row=25, column=index, value="")
                cpUserOperationReportTable.cell(row=26, column=index, value=0)
                cpUserOperationReportTable.cell(row=27, column=index, value="")
            else:
                bp = bps[0]
                cpUserOperationReportTable.cell(row=24, column=index, value=aesCipher.decrypt(bp.bpHighIndex))
                cpUserOperationReportTable.cell(row=25, column=index,
                                                 value=az_dateutil.timestamp2datetime(bp.createDate).strftime("%d %b %Y"))
                cpUserOperationReportTable.cell(row=26, column=index, value=aesCipher.decrypt(bp.bpLowIndex))
                cpUserOperationReportTable.cell(row=27, column=index,
                                                 value=az_dateutil.timestamp2datetime(bp.createDate).strftime("%d %b %Y"))
            #胆固醇
            cholestrols = db_dashboard.Cholesterol.objects(userPhone=user.userPhone)
            if len(cholestrols) == 0:
                cpUserOperationReportTable.cell(row=28, column=index, value=0)
                cpUserOperationReportTable.cell(row=29, column=index, value="")
                cpUserOperationReportTable.cell(row=30, column=index, value=0)
                cpUserOperationReportTable.cell(row=31, column=index, value="")
                cpUserOperationReportTable.cell(row=32, column=index, value=0)
                cpUserOperationReportTable.cell(row=33, column=index, value="")
            else:
                cholestrol = cholestrols[0]
                cpUserOperationReportTable.cell(row=28, column=index, value=aesCipher.decrypt(cholestrol.low))
                cpUserOperationReportTable.cell(row=29, column=index,
                                                 value=az_dateutil.timestamp2datetime(cholestrol.createDate).strftime("%d %b %Y"))
                cpUserOperationReportTable.cell(row=30, column=index, value=aesCipher.decrypt(cholestrol.high))
                cpUserOperationReportTable.cell(row=31, column=index,
                                                 value=az_dateutil.timestamp2datetime(cholestrol.createDate).strftime("%d %b %Y"))
                cpUserOperationReportTable.cell(row=32, column=index, value=aesCipher.decrypt(cholestrol.tc))
                cpUserOperationReportTable.cell(row=33, column=index,
                                                 value=az_dateutil.timestamp2datetime(cholestrol.createDate).strftime("%d %b %Y"))
            #BMI值(最近一次得到BMI值)
            bmis = db_dashboard.Bmi.objects(userPhone=user.userPhone)
            if len(bmis) == 0:
                cpUserOperationReportTable.cell(row=34, column=index, value=0)
                cpUserOperationReportTable.cell(row=35, column=index, value="")
                cpUserOperationReportTable.cell(row=36, column=index, value=0)
                cpUserOperationReportTable.cell(row=37, column=index, value="")
            else:
                bmi = bmis[0]
                cpUserOperationReportTable.cell(row=34, column=index, value=aesCipher.decrypt(bmi.bmiIndex))
                cpUserOperationReportTable.cell(row=35, column=index,
                                                 value=az_dateutil.timestamp2datetime(bmi.createDate).strftime("%d %b %Y"))
                cpUserOperationReportTable.cell(row=36, column=index, value=aesCipher.decrypt(bmi.targetBmiIndex))
                cpUserOperationReportTable.cell(row=37, column=index,
                                                 value=az_dateutil.timestamp2datetime(bmi.createDate).strftime("%d %b %Y"))

            #本周运动分钟/个人设定运动指标（大等于1，Y；小于1，N)
            tz = pytz.timezone(pytz.country_timezones('cn')[0])
            thisWeekLastDayTimestamp = rest.thisWeekLastDayTimestamp(datetime.fromtimestamp(now, tz))
            exericeLogs7 = db_dashboard.queryExericeLogBetweenDays(thisWeekLastDayTimestamp,
                                                                   thisWeekLastDayTimestamp - 7 * 24 * 60 * 60,
                                                                   user.userPhone)
            thisWeekTarget = 150.0
            thisWeekMin = 0.0
            for exericeLog in exericeLogs7:
                thisWeekMin = exericeLog.totalMinute + thisWeekMin
                thisWeekTarget = exericeLog.targetTotalMinute
            cpUserOperationReportTable.cell(row=22, column=index, value="Y" if thisWeekMin >= thisWeekTarget else "N")
            #上周设定的最后一个目标运动时间
            lastExericeLogs7 = db_dashboard.queryExericeLogBetweenDays(thisWeekLastDayTimestamp - 7 * 24 * 60 * 60,
                                                                       thisWeekLastDayTimestamp - 14 * 24 * 60 * 60,
                                                                       user.userPhone)
            lastWeekTarget = 150.0
            lastWeekMin = 0.0
            for exericeLog in lastExericeLogs7:
                lastWeekMin = exericeLog.totalMinute + lastWeekMin
                lastWeekTarget = exericeLog.targetTotalMinute
            cpUserOperationReportTable.cell(row=21, column=index, value=lastWeekTarget)
            #上个自然周运动时间
            cpUserOperationReportTable.cell(row=20, column=index, value=lastWeekMin)
            #Exercises Trends(Activity Minutes this week/Activity Minutes last week)
            if thisWeekMin == 0.0:
                cpUserOperationReportTable.cell(row=19, column=index, value=0)
            if lastWeekMin == 0.0:
                cpUserOperationReportTable.cell(row=19, column=index, value=1)
            else:
                cpUserOperationReportTable.cell(row=19, column=index, value=thisWeekMin/lastWeekMin)
            #已经完整回答的临床问卷数量
            cpUserOperationReportTable.cell(row=5, column=index, value=db_engine.SurveyResult.objects(userPhone=user.userPhone).count())
            #已经推送的临床问卷数量
            surveySchedules = surveyInstanceDB.SurveySchedule.objects(userPhone=user.userPhone)
            if surveySchedules.count()>0:
                surveySchedule = surveySchedules[0]
                dayInterval = surveyInstanceDB.calDayIntervalForExport(surveySchedule.firstDone, now)
                if dayInterval == 0 and aesCipher.decrypt(surveySchedule.currentSurvey) == 'BMQ-G':
                    cpUserOperationReportTable.cell(row=6, column=index, value=1)
                    cpUserOperationReportTable.cell(row=7, column=index, value="=%s/%s" % (excel_style(5, index), excel_style(6, index)))
                elif dayInterval == 0 and aesCipher.decrypt(surveySchedule.currentSurvey) == 'LSQ-V1':
                    cpUserOperationReportTable.cell(row=6, column=index, value=2)
                    cpUserOperationReportTable.cell(row=7, column=index, value=
                                                   "=%s/%s" % (excel_style(5, index), excel_style(6, index)))
                elif dayInterval == 3:
                    cpUserOperationReportTable.cell(row=6, column=index, value=2)
                    cpUserOperationReportTable.cell(row=7, column=index, value=
                                                   "=%s/%s" % (excel_style(5, index), excel_style(6, index)))
                elif dayInterval == 7:
                    cpUserOperationReportTable.cell(row=6, column=index, value=3)
                    cpUserOperationReportTable.cell(row=7, column=index, value=
                                                   "=%s/%s" % (excel_style(5, index), excel_style(6, index)))
                elif dayInterval == 11:
                    cpUserOperationReportTable.cell(row=6, column=index, value=4)
                    cpUserOperationReportTable.cell(row=7, column=index, value=
                                                   "=%s/%s" % (excel_style(5, index), excel_style(6, index)))
                elif dayInterval == 15:
                    cpUserOperationReportTable.cell(row=6, column=index, value=5)
                    cpUserOperationReportTable.cell(row=7, column=index, value=
                                                   "=%s/%s" % (excel_style(5, index), excel_style(6, index)))
                elif dayInterval == 19:
                    cpUserOperationReportTable.cell(row=6, column=index, value=6)
                    cpUserOperationReportTable.cell(row=7, column=index, value=
                                                   "=%s/%s" % (excel_style(5, index), excel_style(6, index)))
                elif dayInterval == 23:
                    cpUserOperationReportTable.cell(row=6, column=index, value=7)
                    cpUserOperationReportTable.cell(row=7, column=index, value=
                                                   "=%s/%s" % (excel_style(5, index), excel_style(6, index)))
                elif dayInterval == 24:
                    if aesCipher.decrypt(surveySchedule.currentSurvey) == 'RAQ':
                        cpUserOperationReportTable.cell(row=6, column=index, value=8)
                        cpUserOperationReportTable.cell(row=7, column=index, value=
                                                   "=%s/%s" % (excel_style(5, index), excel_style(6, index)))
                    elif aesCipher.decrypt(surveySchedule.currentSurvey) == 'BMQ-G':
                        cpUserOperationReportTable.cell(row=6, column=index, value=9)
                        cpUserOperationReportTable.cell(row=7, column=index, value=
                                                   "=%s/%s" % (excel_style(5, index), excel_style(6, index)))
                    elif aesCipher.decrypt(surveySchedule.currentSurvey) == 'LSQ-V2':
                        cpUserOperationReportTable.cell(row=6, column=index, value=10)
                        cpUserOperationReportTable.cell(row=7, column=index, value=
                                                   "=%s/%s" % (excel_style(5, index), excel_style(6, index)))
            else:
                cpUserOperationReportTable.cell(row=6, column=index, value=0)
                cpUserOperationReportTable.cell(row=7, column=index, value=0)

            #健康指数的值
            myHealthIndex = rest.calHealthIndex(user.userPhone)
            cpUserOperationReportTable.cell(row=14, column=index, value=myHealthIndex)
            healthIndexCount = 0
            baseHealthIndexCount = 0.0000000001
            for u in users:
                if u.patientCode:
                    if not u.userPhone == user.userPhone:
                        if not healthDic.has_key(u.userPhone):
                            thisHealthIndex = rest.calHealthIndex(u.userPhone)
                            healthDic[u.userPhone] = thisHealthIndex
                        else:
                            thisHealthIndex = healthDic[u.userPhone]
                        if myHealthIndex > thisHealthIndex:
                            healthIndexCount += 1
                        baseHealthIndexCount += 1
            print '------------------'
            print healthIndexCount
            print baseHealthIndexCount
            print '=================='
            cpUserOperationReportTable.cell(row=15, column=index, value='%s%%' % (healthIndexCount*1.0/baseHealthIndexCount*100.0))
            #完整回答健康问答次数
            cpUserOperationReportTable.cell(row=9, column=index, value=topic.QuestionRecord.objects(userPhone=user.userPhone).count())
            #进入健康问答次数
            questionPopularities = api_statistics.QuestionPopularity.objects(userPhone=user.userPhone)
            if questionPopularities.count() == 0:
                cpUserOperationReportTable.cell(row=8, column=index, value=0)
            else:
                cpUserOperationReportTable.cell(row=8, column=index, value=questionPopularities[0].totalCount)
            #访问频率
            homePopularities = api_statistics.HomePopularity.objects(userPhone=user.userPhone)
            if homePopularities.count() == 0:
                cpUserOperationReportTable.cell(row=16, column=index, value=0)
                cpUserOperationReportTable.cell(row=17, column=index, value=0)
            else:
                homePopularities7 = db_dashboard.queryHomeLogBetweenDays(now - 7 * 24 * 60 * 60, now, user.userPhone)
                if homePopularities7.count() == 0:
                    cpUserOperationReportTable.cell(row=16, column=index, value=0)
                else:
                    cpUserOperationReportTable.cell(row=16, column=index, value=homePopularities7.count())
                cpUserOperationReportTable.cell(row=17, column=index, value=homePopularities.count())

            index += 1

    wb.save("static/EventTrackerReport_v1.xlsx")


def open_excel(file='file.xlsx', page=0):
    db_engine.connect("az", host=db_engine.host)
    data = xlrd.open_workbook(file)
    table = data.sheets()[msgSheetIndex]

    wb = copy(data)
    cpTable = wb.get_sheet(msgSheetIndex)
    for i in range(2, 65, 1):
        msgNo = int(table.cell(i, 0).value)
        print(msgNo)
        lMsgs = api_statistics.LongMessagePopularity.objects(msgNo=str(msgNo))
        userCount = 0
        if len(lMsgs) == 0:
            lMsg = api_statistics.LongMessagePopularity(totalCount=0)
        else:
            lMsg = lMsgs[0]
            userCount = len(lMsg.users)
        cpTable.write(i, 1, lMsg.totalCount)
        cpTable.write(i, 2, userCount)
        if userCount == 0:
            cpTable.write(i, 3, 0)
        else:
            cpTable.write(i, 3, xlwt.Formula("C%s/B%s" % (i + 1, i + 1)))

    for i in range(2, 10, 1):
        index = i + 8
        print 'index %s' % index
        lCats = api_statistics.LongMessageCatalogPopularity.objects(catalogNo=str(index))
        userCount = 0
        if len(lCats) == 0:
            lCat = api_statistics.LongMessageCatalogPopularity(totalCount=0)
        else:
            lCat = lCats[0]
            userCount = len(lCat.users)
        cpTable.write(i, 9, lCat.totalCount)
        cpTable.write(i, 10, userCount)
        if userCount == 0:
            cpTable.write(i, 11, 0)
        else:
            cpTable.write(i, 11, xlwt.Formula("K%s/J%s" % (i + 1, i + 1)))

    cpDetailedAdheranceDateTable = wb.get_sheet(detailedAdheranceDateIndex)
    cpAdheranceDateTable = wb.get_sheet(adheranceDateIndex)
    cpUserOperationReportTable = wb.get_sheet(userOperationReportIndex)
    offset = page * pageLength
    print "offset: %s" % offset
    print "pageLength: %s" % pageLength
    users = db_engine.User.objects(userType__ne=2).order_by('createDate').skip(offset).limit(pageLength)
    print "===== total count : %s =====" % users.count()
    index = 3
    aesCipher = aes.AESCipher(aes.aes_key)
    healthDic = {}
    for i in range(0, users.count()):
        user = users[i]
        print user.patientCode
        if True:
            # patientCode
            cpDetailedAdheranceDateTable.write(1, index, user.patientCode)
            cpAdheranceDateTable.write(1, index, user.patientCode)
            cpUserOperationReportTable.write(1, index, user.patientCode)
            # sexy
            cpDetailedAdheranceDateTable.write(2, index,
                                               "Male" if aesCipher.decrypt(user.userGender) == "1" else "Female")
            cpAdheranceDateTable.write(2, index, "Male" if aesCipher.decrypt(user.userGender) == "1" else "Female")
            # year old
            birthday = aesCipher.decrypt(user.userBirthday)
            if birthday:
                print birthday
                try:
                    if "-" in birthday:
                        birthdayDate = datetime.strptime(birthday, '%Y-%m-%d')
                    elif "." in birthday:
                        birthdayDate = datetime.strptime(birthday, '%Y.%m.%d')
                    else:
                        birthdayDate = datetime.strptime(birthday, '%Y%m%d')
                    cpDetailedAdheranceDateTable.write(3, index, calculate_age(birthdayDate))
                    cpAdheranceDateTable.write(3, index, calculate_age(birthdayDate))
                    cpUserOperationReportTable.write(3, index, calculate_age(birthdayDate))
                except Exception as err:
                    print str(err)

            # Adherance day since registration
            medicines = db_engine.Medicine.objects(userPhone=user.userPhone).order_by('-createDate')
            count = 0
            medicineList = []
            for medicine in medicines:
                if aesCipher.decrypt(medicine.done) == "1":
                    medicineList.append(az_dateutil.timestamp2datetime(medicine.createDate).strftime("%d %b %Y"))
                    count += 1
            cpDetailedAdheranceDateTable.write(4, index, count)
            # number of days since registration
            createDate = az_dateutil.timestamp2datetime(user.createDate)
            diffCreateDate = diffDay(createDate)
            cpDetailedAdheranceDateTable.write(5, index, diffCreateDate)
            cpUserOperationReportTable.write(17, index, diffCreateDate)
            cpUserOperationReportTable.write(9, index, int(diffCreateDate/7))
            cpDetailedAdheranceDateTable.write(6, index,
                                               xlwt.Formula(
                                                   "%s/%s" % (excel_style(5, index + 1), excel_style(6, index + 1))))
            for i in range(0, len(medicineList), 1):
                cpDetailedAdheranceDateTable.write(7 + i, index, medicineList[i])

            # Medicine Adherance(Adherance day in last 30 days)
            now = int(time.time())
            tz = pytz.timezone(pytz.country_timezones('cn')[0])
            thisDate = datetime.fromtimestamp(now, tz)
            now = int(az_dateutil.date2timestamp(
                thisDate.replace(thisDate.year, thisDate.month, thisDate.day, 0, 0, 0))) + 16 * 60 * 60

            medicines30 = db_dashboard.queryMedicinelistBetweenDays(now, now - 30 * 24 * 60 * 60, user.userPhone)
            count = 0
            medicineList = []
            for medicine in medicines30:
                if aesCipher.decrypt(medicine.done) == "1":
                    medicineList.append(az_dateutil.timestamp2datetime(medicine.createDate).strftime("%d %b %Y"))
                    count += 1
            cpAdheranceDateTable.write(4, index, count)
            cpUserOperationReportTable.write(22, index, count)
            for i in range(0, len(medicineList), 1):
                cpAdheranceDateTable.write(5 + i, index, medicineList[i])

            # 是否设置了看护者
            caregivers = db_engine.Caregiver.objects(caredUserPhone=user.userPhone)
            cpUserOperationReportTable.write(2, index, "Y" if len(caregivers) > 0 else "N")
            cpUserOperationReportTable.write(10, index, "Y" if user.isSmoke == 1 else "N")
            cpUserOperationReportTable.write(11, index, "Y" if user.hasHighBp == 1 else "N")
            # 血压值(最近一次输入的高血压值)
            bps = db_dashboard.Bp.objects(userPhone=user.userPhone)
            if len(bps) == 0:
                cpUserOperationReportTable.write(23, index, 0)
                cpUserOperationReportTable.write(24, index, "")
                cpUserOperationReportTable.write(25, index, 0)
                cpUserOperationReportTable.write(26, index, "")
            else:
                bp = bps[0]
                cpUserOperationReportTable.write(23, index, aesCipher.decrypt(bp.bpHighIndex))
                cpUserOperationReportTable.write(24, index,
                                                 az_dateutil.timestamp2datetime(bp.createDate).strftime("%d %b %Y"))
                cpUserOperationReportTable.write(25, index, aesCipher.decrypt(bp.bpLowIndex))
                cpUserOperationReportTable.write(26, index,
                                                 az_dateutil.timestamp2datetime(bp.createDate).strftime("%d %b %Y"))
            #低密度脂蛋白(最近一次输入的LDL值)
            cholestrols = db_dashboard.Cholesterol.objects(userPhone=user.userPhone)
            if len(cholestrols) == 0:
                cpUserOperationReportTable.write(27, index, 0)
                cpUserOperationReportTable.write(28, index, "")
                cpUserOperationReportTable.write(29, index, 0)
                cpUserOperationReportTable.write(30, index, "")
                cpUserOperationReportTable.write(31, index, 0)
                cpUserOperationReportTable.write(32, index, "")
            else:
                cholestrol = cholestrols[0]
                cpUserOperationReportTable.write(27, index, aesCipher.decrypt(cholestrol.low))
                cpUserOperationReportTable.write(28, index,
                                                 az_dateutil.timestamp2datetime(cholestrol.createDate).strftime("%d %b %Y"))
                cpUserOperationReportTable.write(29, index, aesCipher.decrypt(cholestrol.high))
                cpUserOperationReportTable.write(30, index,
                                                 az_dateutil.timestamp2datetime(cholestrol.createDate).strftime("%d %b %Y"))
                cpUserOperationReportTable.write(31, index, aesCipher.decrypt(cholestrol.tc))
                cpUserOperationReportTable.write(32, index,
                                                 az_dateutil.timestamp2datetime(cholestrol.createDate).strftime("%d %b %Y"))
            #BMI值(最近一次得到BMI值)
            bmis = db_dashboard.Bmi.objects(userPhone=user.userPhone)
            if len(bmis) == 0:
                cpUserOperationReportTable.write(33, index, 0)
                cpUserOperationReportTable.write(34, index, "")
                cpUserOperationReportTable.write(35, index, 0)
                cpUserOperationReportTable.write(36, index, "")
            else:
                bmi = bmis[0]
                cpUserOperationReportTable.write(33, index, aesCipher.decrypt(bmi.bmiIndex))
                cpUserOperationReportTable.write(34, index,
                                                 az_dateutil.timestamp2datetime(bmi.createDate).strftime("%d %b %Y"))
                cpUserOperationReportTable.write(35, index, aesCipher.decrypt(bmi.targetBmiIndex))
                cpUserOperationReportTable.write(36, index,
                                                 az_dateutil.timestamp2datetime(bmi.createDate).strftime("%d %b %Y"))
            #本周运动分钟/个人设定运动指标（大等于1，Y；小于1，N)
            tz = pytz.timezone(pytz.country_timezones('cn')[0])
            thisWeekLastDayTimestamp = rest.thisWeekLastDayTimestamp(datetime.fromtimestamp(now, tz))
            exericeLogs7 = db_dashboard.queryExericeLogBetweenDays(thisWeekLastDayTimestamp,
                                                                   thisWeekLastDayTimestamp - 7 * 24 * 60 * 60,
                                                                   user.userPhone)
            thisWeekTarget = 150.0
            thisWeekMin = 0.0
            for exericeLog in exericeLogs7:
                thisWeekMin = exericeLog.totalMinute + thisWeekMin
                thisWeekTarget = exericeLog.targetTotalMinute
            cpUserOperationReportTable.write(21, index, "Y" if thisWeekMin >= thisWeekTarget else "N")
            #上周设定的最后一个目标运动时间
            lastExericeLogs7 = db_dashboard.queryExericeLogBetweenDays(thisWeekLastDayTimestamp - 7 * 24 * 60 * 60,
                                                                       thisWeekLastDayTimestamp - 14 * 24 * 60 * 60,
                                                                       user.userPhone)
            lastWeekTarget = 150.0
            lastWeekMin = 0.0
            for exericeLog in lastExericeLogs7:
                lastWeekMin = exericeLog.totalMinute + lastWeekMin
                lastWeekTarget = exericeLog.targetTotalMinute
            cpUserOperationReportTable.write(20, index, lastWeekTarget)
            #上个自然周运动时间
            cpUserOperationReportTable.write(19, index, lastWeekMin)
            #Exercises Trends(Activity Minutes this week/Activity Minutes last week)
            if thisWeekMin == 0.0:
                cpUserOperationReportTable.write(18, index, 0)
            if lastWeekMin == 0.0:
                cpUserOperationReportTable.write(18, index, 1)
            else:
                cpUserOperationReportTable.write(18, index, thisWeekMin/lastWeekMin)
            #已经完整回答的临床问卷数量
            cpUserOperationReportTable.write(4, index, db_engine.SurveyResult.objects(userPhone=user.userPhone).count())
            #已经推送的临床问卷数量
            surveySchedules = surveyInstanceDB.SurveySchedule.objects(userPhone=user.userPhone)
            if surveySchedules.count()>0:
                surveySchedule = surveySchedules[0]
                dayInterval = surveyInstanceDB.calDayIntervalForExport(surveySchedule.firstDone, now)
                if dayInterval == 0 and aesCipher.decrypt(surveySchedule.currentSurvey) == 'BMQ-G':
                    cpUserOperationReportTable.write(5, index, 1)
                    cpUserOperationReportTable.write(6, index, xlwt.Formula(
                                                   "%s/%s" % (excel_style(5, index + 1), excel_style(6, index + 1))))
                elif dayInterval == 0 and aesCipher.decrypt(surveySchedule.currentSurvey) == 'LSQ-V1':
                    cpUserOperationReportTable.write(5, index, 2)
                    cpUserOperationReportTable.write(6, index, xlwt.Formula(
                                                   "%s/%s" % (excel_style(5, index + 1), excel_style(6, index + 1))))
                elif dayInterval == 3:
                    cpUserOperationReportTable.write(5, index, 2)
                    cpUserOperationReportTable.write(6, index, xlwt.Formula(
                                                   "%s/%s" % (excel_style(5, index + 1), excel_style(6, index + 1))))
                elif dayInterval == 7:
                    cpUserOperationReportTable.write(5, index, 3)
                    cpUserOperationReportTable.write(6, index, xlwt.Formula(
                                                   "%s/%s" % (excel_style(5, index + 1), excel_style(6, index + 1))))
                elif dayInterval == 11:
                    cpUserOperationReportTable.write(5, index, 4)
                    cpUserOperationReportTable.write(6, index, xlwt.Formula(
                                                   "%s/%s" % (excel_style(5, index + 1), excel_style(6, index + 1))))
                elif dayInterval == 15:
                    cpUserOperationReportTable.write(5, index, 5)
                    cpUserOperationReportTable.write(6, index, xlwt.Formula(
                                                   "%s/%s" % (excel_style(5, index + 1), excel_style(6, index + 1))))
                elif dayInterval == 19:
                    cpUserOperationReportTable.write(5, index, 6)
                    cpUserOperationReportTable.write(6, index, xlwt.Formula(
                                                   "%s/%s" % (excel_style(5, index + 1), excel_style(6, index + 1))))
                elif dayInterval == 23:
                    cpUserOperationReportTable.write(5, index, 7)
                    cpUserOperationReportTable.write(6, index, xlwt.Formula(
                                                   "%s/%s" % (excel_style(5, index + 1), excel_style(6, index + 1))))
                elif dayInterval == 24:
                    if aesCipher.decrypt(surveySchedule.currentSurvey) == 'RAQ':
                        cpUserOperationReportTable.write(5, index, 8)
                        cpUserOperationReportTable.write(6, index, xlwt.Formula(
                                                   "%s/%s" % (excel_style(5, index + 1), excel_style(6, index + 1))))
                    elif aesCipher.decrypt(surveySchedule.currentSurvey) == 'BMQ-G':
                        cpUserOperationReportTable.write(5, index, 9)
                        cpUserOperationReportTable.write(6, index, xlwt.Formula(
                                                   "%s/%s" % (excel_style(5, index + 1), excel_style(6, index + 1))))
                    elif aesCipher.decrypt(surveySchedule.currentSurvey) == 'LSQ-V2':
                        cpUserOperationReportTable.write(5, index, 10)
                        cpUserOperationReportTable.write(6, index, xlwt.Formula(
                                                   "%s/%s" % (excel_style(5, index + 1), excel_style(6, index + 1))))
            else:
                cpUserOperationReportTable.write(5, index, 0)
                cpUserOperationReportTable.write(6, index, 0)

            #健康指数的值
            myHealthIndex = rest.calHealthIndex(user.userPhone)
            cpUserOperationReportTable.write(13, index, myHealthIndex)
            healthIndexCount = 0
            baseHealthIndexCount = 0.0000000001
            for u in users:
                if u.patientCode:
                    if not u.userPhone == user.userPhone:
                        if not healthDic.has_key(u.userPhone):
                            thisHealthIndex = rest.calHealthIndex(u.userPhone)
                            healthDic[u.userPhone] = thisHealthIndex
                        else:
                            thisHealthIndex = healthDic[u.userPhone]
                        if myHealthIndex > thisHealthIndex:
                            healthIndexCount += 1
                        baseHealthIndexCount += 1
            print '------------------'
            print healthIndexCount
            print baseHealthIndexCount
            print '=================='
            cpUserOperationReportTable.write(14, index, '%s%%' % (healthIndexCount*1.0/baseHealthIndexCount*100.0))
            #完整回答健康问答次数
            cpUserOperationReportTable.write(8, index, topic.QuestionRecord.objects(userPhone=user.userPhone).count())
            #进入健康问答次数
            questionPopularities = api_statistics.QuestionPopularity.objects(userPhone=user.userPhone)
            if questionPopularities.count() == 0:
                cpUserOperationReportTable.write(7, index, 0)
            else:
                cpUserOperationReportTable.write(7, index, questionPopularities[0].totalCount)
            #访问频率
            homePopularities = api_statistics.HomePopularity.objects(userPhone=user.userPhone)
            if homePopularities.count() == 0:
                cpUserOperationReportTable.write(15, index, 0)
                cpUserOperationReportTable.write(16, index, 0)
            else:
                homePopularities7 = db_dashboard.queryHomeLogBetweenDays(now - 7 * 24 * 60 * 60, now, user.userPhone)
                if homePopularities7.count() == 0:
                    cpUserOperationReportTable.write(15, index, 0)
                else:
                    cpUserOperationReportTable.write(15, index, homePopularities7.count())
                cpUserOperationReportTable.write(16, index, homePopularities.count())

            index += 1
            print 'index %s' % index

    wb.save('static/EventTrackerReport_%s.xls' % page)


LETTERS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'


def excel_style(row, col):
    """ Convert given row and column number to an Excelstyle cell name. """
    result = []
    while col:
        col, rem = divmod(col - 1, 26)
        result[:0] = LETTERS[rem]
    return ''.join(result) + str(row)


def calculate_age(born):
    today = datetime.today()
    try:
        birthday = born.replace(year=today.year)
    except ValueError:  # raised when birth date is February 29 and the current year is not a leap year
        birthday = born.replace(year=today.year, day=born.day - 1)
    if birthday > today:
        return today.year - born.year - 1
    else:
        return today.year - born.year


def diffDay(day):
    today = datetime.today()
    today = today.replace(hour=0, minute=0, second=0, microsecond=0)
    day = day.replace(hour=0, minute=0, second=0, microsecond=0)
    return (today - day).days


if __name__ == "__main__":
    # db_engine.connect("az", host=db_engine.host)
    # users = db_engine.User.objects(userType__ne=2).order_by('-createDate')
    # print users.count()
    # for i in range(0, users.count()/pageLength + 1):
    #     print 'page %s' % i
    #     open_excel(file='file.xlsx', page=i)

    open_excelByOpenXL(file='file.xlsx')